package org.dt.common.sql;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class SqlScriptRunner {
	private Logger log = LoggerFactory.getLogger(SqlScriptRunner.class);

	@SuppressWarnings("unused")
	private DataBaseType type = DataBaseType.MYSQL;

	private SqlScriptRunner() {
	}

	public static SqlScriptRunner create(DataBaseType type) {
		SqlScriptRunner runner = new SqlScriptRunner();
		runner.type = type;
		return runner;
	}

	public boolean runScript(Connection conn, String fileName) {
		try {
			return runScript(conn, new FileInputStream(fileName));
		} catch (FileNotFoundException e) {
			log.error(e.getMessage(), e);
			return false;
		}
	}

	public boolean runScript(Connection conn, InputStream resource) {
		try (
		BufferedReader in = new BufferedReader(new InputStreamReader(resource))) {
			while (true) {
				StringBuilder sqlSegment = new StringBuilder();
				while (true) {
					String line = in.readLine();
					if (line == null) {
						return true;
					}
					line = line.trim();

					// Ignore comments and blank lines.
					if (!isComment(line)) {
						sqlSegment.append(" ").append(line);
					}
					if (line.endsWith(";")) {
						break;
					}
				}
				String sql = sqlSegment.toString().trim();
				executeSql(conn, sql);
			}

		} catch (IOException | SQLException e) {
			log.error(e.getMessage(), e);
			return false;
		}

	}

	private void executeSql(Connection conn, String sql) throws SQLException {
		if (!sql.equals("")) {
			try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
				pstmt.execute();
			} catch (SQLException e) {
				throw e;
			}
		}
	}

	private boolean isComment(String line) {
		if (line.equals("")) {
			return true;
		}
		// "//" is HSQLDB
		// "--" is DB2 and Postgres
		// "#" is MySQL
		// "REM" is Oracle
		// "/*" is SQLServer
		return line.startsWith("//") || line.startsWith("--")
				|| line.startsWith("#") || line.startsWith("REM")
				|| line.startsWith("/*") || line.startsWith("*");
	}

	public static enum DataBaseType {
		MSSQL, SYBASE, ORACLE, MYSQL, DB2;
	}
}
